package com.zygh.lz.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.lz.entity.signfile.SignGeneral;
import com.zygh.lz.entity.signfile.SignGenerals;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SignGeneralMapper extends BaseMapper<SignGeneral> {

    int insertIgnoreNull(SignGeneral signGeneral);

    @Select("<script>" +
            " SELECT IFNULL(SUM(sign_num),0) FROM sign_general " +
            "<where>  " +
            "TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='deptName != null'>" +
            "   and section_name like  CONCAT('%',#{deptName},'%') " +
            "</if>" +
            "</where> " +
            "</script>")
    int findSignNum(@Param("time") String time,
                    @Param("deptName") String deptName);

    @Select("<script> " +
            "SELECT IFNULL(SUM(police_num),0) FROM sign_general " +
            "<where>  TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='deptName != null'>" +
            "and section_name like  CONCAT('%',#{deptName},'%') " +
            "</if>" +
            "</where> " +
            "</script>")
    int findPoliceNum(@Param("time") String time,
                      @Param("deptName") String deptName);


    @Select("<script> " +
            "SELECT IFNULL(SUM(mileage),0) FROM sign_general <where>  TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='deptName != null'>" +
            "and section_name like  CONCAT('%',#{deptName},'%')" +
            "</if>" +
            "</where> " +
            "</script>")
    int findSignMileage(@Param("time") String time,
                        @Param("deptName") String deptName);

    @Select("<script> " +
            "SELECT IFNULL(SUM(duration),0) FROM sign_general <where>  TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='deptName != null'>" +
            "and section_name like  CONCAT('%',#{deptName},'%')" +
            "</if>" +
            "</where> " +
            "</script>")
    int findSignDuration(@Param("time") String time,
                         @Param("deptName") String deptName);


    @Select("<script>" +
            " SELECT IFNULL(SUM(site_illegal),0) FROM sign_general " +
            "<where>  TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='deptName != null'>" +
            "and section_name like  CONCAT('%',#{deptName},'%')" +
            "</if>" +
            "</where> " +
            "</script>")
    int findSignSiteIllegal(@Param("time") String time,
                            @Param("deptName") String deptName);


    @Select("<script> " +
            "SELECT IFNULL(SUM(offsite_illegal),0) FROM sign_general " +
            "<where>" +
            " TO_DAYS(sign_time) = TO_DAYS(#{time})" +
            "<if test='deptName != null'>" +
            " and section_name like  CONCAT('%',#{deptName},'%')" +
            "</if>" +
            "</where> " +
            "</script>")
    int findSignoffsiteIllegal(@Param("time") String time,
                               @Param("deptName") String deptName);


    @Select("<script>  " +
            "SELECT IFNULL(SUM(jiechu),0) FROM sign_general " +
            "<where> TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='deptName != null'>" +
            "and section_name like  CONCAT('%',#{deptName},'%')" +
            "</if>" +
            "</where> " +
            "</script>")
    int findSignJiechu(@Param("time") String time,
                       @Param("deptName") String deptName);


    @Select("<script> " +
            "SELECT IFNULL(SUM(problem),0) FROM sign_general <where> TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='deptName != null'>" +
            "and section_name like  CONCAT('%',#{deptName},'%')" +
            "</if>" +
            "</where> " +
            "</script>")
    int findSignProblem(@Param("time") String time,
                        @Param("deptName") String deptName);


    @Select("<script> " +
            "SELECT section_name,sign_num,police_num,police_rate,duration,mileage,site_illegal,offsite_illegal,jiechu,problem FROM sign_general " +
            "<where> " +
            "TO_DAYS(sign_time) = TO_DAYS(#{time}) " +
            "<if test='sectionId != null'>" +
            "and section_id = #{sectionId}" +
            "</if>" +
            "</where>" +
            "</script>")
    List<SignGenerals> findSignGeneral(@Param("sectionId") Integer sectionId,
                                       @Param("time") String time);


    @Select("<script>" +
            "SELECT section_name,section_id,SUM(sign_num) sign_num,IFNULL(SUM(police_num),0) police_num,SUM(site_illegal) site_illegal," +
            "SUM(offsite_illegal) offsite_illegal,SUM(jiechu) jiechu,SUM(problem) problem,SUM(total_time) total_time," +
            "SUM(total_distance) total_distance,total_forces, sum_forces,ROUND(SUM(police_rate),2)  police_rate,SUM(post_num) postNum, " +
            "SUM(post_sign) postSign,SUM(location_police) location_police,SUM(location_police_rate) location_police_rate " +
            " FROM sign_general \n" +
            "<where> " +
            "sign_time BETWEEN #{startTime} AND #{endTime} " +
            "<if test='sectionId != null'>" +
            "and section_id = #{sectionId}" +
            "</if>" +
            "</where> " +
            "GROUP BY section_id ORDER BY id ASC" +
            "</script>")
    List<SignGeneral> findSignGeneralByWeek(@Param("sectionId") Integer sectionId,
                                            @Param("startTime") String startTime,
                                            @Param("endTime") String endTime);


}
